package com.cskaoyan.mall.dao;

import com.cskaoyan.mall.model.bo.PageOrdersBO;
import com.cskaoyan.mall.model.bo.UpdateOrderBO;
import com.cskaoyan.mall.model.vo.*;
import com.cskaoyan.mall.utils.DruidUtils;
import com.cskaoyan.mall.utils.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OrdersDaoImpl implements OrdersDao{
    /**
     * 查询总共的orders数据的个数
     * @param pageOrdersBO
     * @return
     */
    @Override
    public Integer getTotalCount(PageOrdersBO pageOrdersBO) {
        Map map = getDynamicSqlAndParams(pageOrdersBO);
        String sql = (String) map.get("sql");
        List params = (List) map.get("params");
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        //查询count值，jdbc返回的就是long类型
        Long query = null;
        try {
            query = (Long) runner.query("select count(id) from orders " + sql,new ScalarHandler(),params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query.intValue();
    }

    /**
     * 拼接sql
     * @param pageOrdersBO
     * @return
     */
    private Map getDynamicSqlAndParams(PageOrdersBO pageOrdersBO) {
        String baseSql = "where 1=1 ";
        List params = new ArrayList();
        if (!StringUtils.isEmpty(pageOrdersBO.getAddress())) {
            baseSql += " and address like ? ";
            params.add("%" + pageOrdersBO.getAddress() + "%");
        }
        if (!StringUtils.isEmpty(pageOrdersBO.getGoods())) {
            baseSql += " and goods like ? ";
            params.add("%" + pageOrdersBO.getGoods() + "%");
        }
        if (!StringUtils.isEmpty(pageOrdersBO.getMoneyLimit1())) {
            baseSql += " and amount <= ? ";
            params.add(pageOrdersBO.getMoneyLimit1());
        }
        if (!StringUtils.isEmpty(pageOrdersBO.getMoneyLimit2())) {
            baseSql += " and amount >= ? ";
            params.add(pageOrdersBO.getMoneyLimit2());
        }
        if (!StringUtils.isEmpty(pageOrdersBO.getId())) {
            baseSql += " and id = ? ";
            params.add(pageOrdersBO.getId());
        }
        if (!StringUtils.isEmpty(pageOrdersBO.getName())) {
            baseSql += " and nickname like ? ";
            params.add("%" + pageOrdersBO.getName() + "%");
        }
        if (pageOrdersBO.getState() != -1) {
            baseSql += " and stateId = ? ";
            params.add(pageOrdersBO.getState());
        }
        HashMap<Object,Object> map = new HashMap<>();
        map.put("sql",baseSql);
        map.put("params",params);
        return map;
    }

    /**
     * 分页查询
     * // select xxx,xxx,xxxx from orders where 1=1 and xxxxx limit xx offset xx
     * @param pageOrdersBO
     * @return
     */
    @Override
    public List<PageOrdersVO> getOrderByPage(PageOrdersBO pageOrdersBO) {
        Map map = getDynamicSqlAndParams(pageOrdersBO);
        String suffix = (String) map.get("sql");
        List params = (List) map.get("params");
        String sql = "select * from orders " + suffix + " limit ? offset ? ";
        params.add(pageOrdersBO.getPagesize());
        params.add((pageOrdersBO.getCurrentPage() - 1 ) * pageOrdersBO.getPagesize());
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<PageOrdersVO> ordersVOS = null;
        try {
            ordersVOS = runner.query(sql,new BeanListHandler<>(PageOrdersVO.class),params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ordersVOS;
    }

    /**
     * 查询订单详情，之后在后面的update中进行修改
     * @param id 订单的id
     * @return
     */
    @Override
    public GetOrderVO order(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        GetOrderVO getOrderVO = null;
        List<OrderSpecVO> spec = null;
        GetGoodsIdVO goodsId = null;
        List<StateVO> states = null;
        OrdersSpecIdVO ordersSpecIdVO = null;
        OrderSpecNameVO orderSpecNameVO = null;
        try {
            //获取商品ID
            goodsId = runner.query("select goodsId from orders where id = ? ",new BeanHandler<>(GetGoodsIdVO.class),id);
            //获取订单详情
            getOrderVO = runner.query("select id , amount , goodsNum as num , goodsDetailId , stateId , goods from orders where id = ? ",new BeanHandler<>(GetOrderVO.class),id);
            //获取当前的规格详情
            orderSpecNameVO = runner.query("select spec from orders where id = ? ",new BeanHandler<>(OrderSpecNameVO.class),id);
            //当前商品的所有规格
            spec = runner.query("select id , specName , unitPrice from spec where goodsId = ? ",new BeanListHandler<>(OrderSpecVO.class),goodsId.getGoodsId());
            getOrderVO.setSpec(spec);
            //订单的所有状态
            states = runner.query("select * from states",new BeanListHandler<>(StateVO.class));
            getOrderVO.setStates(states);
            //获取当前订单的specId
            ordersSpecIdVO = runner.query("select id from spec where goodsId = ? and specname = ? ",new BeanHandler<>(OrdersSpecIdVO.class),goodsId.getGoodsId(),orderSpecNameVO.getSpec());
            getOrderVO.setCurSpec(ordersSpecIdVO);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return getOrderVO;
    }

    @Override
//    {id: "2137", state: 1, spec: 1233, num: 1}
    public int changeOrder(UpdateOrderBO updateOrderBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        GetSpecNameVO getSpecNameVO = null;
        try {
            getSpecNameVO = runner.query("select specname from spec where id = ? ",
                    new BeanHandler<>(GetSpecNameVO.class),updateOrderBO.getSpec());
            runner.update("update orders set stateId = ? , spec = ? , goodsNum = ? where id = ? ",
                    updateOrderBO.getState(),
                    getSpecNameVO.getSpecname(),
                    updateOrderBO.getNum(),
                    updateOrderBO.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }

    @Override
    public int deleteOrder(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from orders where id = ? ",id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 404;
        }
        return 200;
    }
}
